import pymssql

class SQL():
    def __init__(self, connect, mapping_rule):
        self.connect = connect
        self.cursor = self.connect.cursor(as_dict=True)

        self.param = ""
        self.condition = ""
        self.mapping_rule = mapping_rule

    def insert(self, table, dict):
        self.formatSQLString(dict)
        self.formatParam(dict)
        sql = f"""
            INSERT INTO {table} VALUES({self.param})
        """
        self.cursor.execute(sql)

    def delete(self, table, condition):
        # 暂时不支持动态插入变量
        sql = f"""
            DELETE FROM {table} WHERE {condition} 
        """
        self.cursor.execute(sql)

    def select(self, table, column=None, condition=None, order_condition=None):
        """
        :param table:
        :param column: 字典格式
        :param condition: 字典格式{'key': 'value'},key为数据库中key
        :param order_condition:
        :return:
        """
        # 多表数据的获取
        sql_map_base = """
            SELECT * FROM employee, department, post, academy,status
            WHERE employee.department_id=department.department_id AND employee.post_id=post.post_id AND
            employee.academy_id=academy.academy_id AND employee.status_id = status.status_id
        """
        self.cursor.execute(sql_map_base)
        map_base = self.cursor.fetchall()
        # 编码的转换
        for dit in map_base:
            for key in dit:
                if isinstance(dit[key], str):
                    dit[key] = dit[key].encode('latin-1').decode('GBK')
        if column is None:
            self.param = '*'
        else:
            self.formatParam(column)
        if condition:
            # 顺序不能变
            self.formatSQLString(condition)
            condition = self.formatCondition(condition)
            if order_condition is not None:
                sql = f"""
                    SELECT {self.param} FROM {table} WHERE {condition} {order_condition}
                """
            else:
                sql = f"""
                    SELECT {self.param} FROM {table} WHERE {condition}
                """
        else:
            if order_condition is not None:
                sql = f"""
                    SELECT {self.param} FROM {table} {order_condition}
                """
            else:
                sql = f"""
                    SELECT {self.param} FROM {table}
                """
        self.cursor.execute(sql)
        data = self.cursor.fetchall()
        #解决中文乱码问题 不知道为什么返回的数据又不乱码了
        data2 = []
        for dit in data:
            for key in dit:
                if isinstance(dit[key], str):
                    dit[key] = dit[key].encode('latin-1').decode('GBK')
        # 外键的转换
        for dit in data:
            for key in dit:
                if key in ['department_id', 'post_id', 'status_id']:
                    new_key = key[0:-2]
                    new_key += 'name'
                    for dit_converge in map_base:
                        if dit_converge[key] == dit[key]:
                            dit[key] = dit[key] + ' ' + dit_converge[new_key]
                            break
                elif key in ['academy_id']:
                    new_key = key[0:-2]
                    new_key += 'graduate_school'
                    for dit_converge in map_base:
                        if dit_converge[key] == dit[key]:
                            dit[key] = dit[key] + ' ' + dit_converge[new_key]
                            break
        return data

    def update(self, table, updated_value, condition):
        """
        :param table: 字符串
        :param updated_value: column=value 每次只能包含一个
        :param condition: 字典格式
        :return:
        """
        condition = self.formatCondition(condition)
        sql = f"""
            UPDATE {table} SET {updated_value} WHERE {condition}
        """
        self.cursor.execute(sql)

    def commit(self):
        self.connect.commit()

    def formatParam(self, dict):
        self.param = ""
        for key in dict:
            self.param = self.param + str(dict[key]) + ","
        self.param = self.param.strip(',')

    def formatSQLString(self, dict):
        # 为字符串参数加上单引号
        for key in dict:
            if key == "现役员工" or key == "历史员工":
                continue
            elif isinstance(dict[key], str) and key not in ('employee.employee_id', 'employee.status_id',
                                                            'employee.salary_id', 'employee.academy_id',
                                                            'employee.department_id', 'employee.post_id'):
                dict[key] = "'" + dict[key] + "'"

    def close(self):
        self.cursor.close()
        self.connect.close()

    # 格式key: value
    # key为窗体中的名字需要进行映射转换
    # value格式： 'value OR|AND'
    def formatCondition(self, dit):
        condition = ""
        for key in dit:
            if key == "现役员工" or key == "历史员工":
                condition += dit[key] + ' AND '
            else:
                condition += key + "=" + dit[key] + " AND "
        condition = condition.strip(' AND ')
        return condition
